package com.authine.cloudpivot.ext.service.impl;

import com.alibaba.fastjson.JSON;
import com.authine.cloudpivot.engine.api.exceptions.ServiceException;
import com.authine.cloudpivot.engine.domain.bizservice.BizDatabaseConnectionPool;
import com.authine.cloudpivot.engine.enums.ErrCode;
import com.authine.cloudpivot.engine.service.bizservice.BizDatabaseConnectionPoolService;
import com.authine.cloudpivot.engine.service.datasource.DataSourceService;
import com.authine.cloudpivot.engine.service.datasource.MyColumnMapRowMapper;
import com.authine.cloudpivot.engine.service.impl.datasource.TemplateDynamicMultiDataSource;
import com.authine.cloudpivot.ext.service.CloudSqlService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author hxd
 * @Date 2022/9/21 9:48
 * @Description
 **/
@Service
@Slf4j
public class CloudSqlServiceImpl implements CloudSqlService {
    @Autowired
    private BizDatabaseConnectionPoolService connectionPoolService;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public int update(String dbCode, String sql, Map<String, ?> paramMap) {
        try {
            int num = this.getDataSourceService(dbCode).getJdbcTemplate().update(sql, paramMap);
            return num;
        } catch (Exception e) {
            log.info(e.getMessage(), e);
            return 0;
        }
    }

    @Override
    public DataSourceService getDataSourceService(String dbConnPoolCode) {

        BizDatabaseConnectionPool connectionPool = this.connectionPoolService.getByCode(dbConnPoolCode);

        if (connectionPool == null) {
            throw new ServiceException(ErrCode.BIZ_DATA_BASE_POOL_CODE_INVALID, "数据库连接池编码无效");
        } else {
            String datasourceType = connectionPool.getDatasourceType();
            String databaseType = connectionPool.getDatabaseType();
            String type = datasourceType + "." + databaseType;
            DataSourceService dataSourceService = (DataSourceService) TemplateDynamicMultiDataSource.getDataSourceService(dbConnPoolCode, type, JSON.toJSONString(connectionPool));
            return dataSourceService;
        }

    }

    @Override
    public List<Map<String, Object>> getList(String dbCode, String sql) {
        List<Map<String, Object>> resultList = this.getDataSourceService(dbCode).getJdbcTemplate().query(sql, new HashMap<>(), new MyColumnMapRowMapper());

        return resultList;
    }

    @Override
    public Map<String, Object> getMap(String dbCode, String sql) {

        try {
            Map<String, Object> map = this.getDataSourceService(dbCode).getJdbcTemplate().queryForObject(sql, new HashMap<>(), new MyColumnMapRowMapper());
            return map;
        } catch (Exception e) {
            log.info(e.getMessage(), e);
            return new HashMap<>();
        }
    }

    @Override
    public List<Map<String, Object>> getList(String sql) {
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        return list;
    }

    @Override
    public Map<String, Object> getMap(String sql) {

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        if (list.isEmpty()) {
            return Collections.emptyMap();
        }
        return list.get(0);
    }


    @Override
    public int getCount(final String sql) {
        Integer integer = 0;
        try {
            integer = jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return integer;
    }

    @Override
    public int update(final String sql) {
        int i = jdbcTemplate.update(sql);
        return i;
    }

    public void execute(String dbCode, String sql) {
        this.getDataSourceService(dbCode).getJdbcTemplate().getJdbcTemplate().execute(sql);
    }

    public JdbcTemplate getJdbcTemplateByDbCode(String dbCode) {
        return this.getDataSourceService(dbCode).getJdbcTemplate().getJdbcTemplate();
    }

//    /**
//     * 此种写法适合：存储过程返回一个结果集
//     *
//     * @param dbCode
//     * @param sql
//     * @return
//     */
//    public String executeAndGetReturnValue(String dbCode, String sql) {
//        JdbcTemplate jdbcTemplate = getJdbcTemplateByDbCode(CloudSqlService.htEas);
//
//        String returnVale = (String) jdbcTemplate.execute(
//                new CallableStatementCreator() {
//                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
//                        CallableStatement cs = con.prepareCall("{call [HG_LINK].[hg].[dbo].SyncToDoList (?,?,?,?,?,?,?,?,?,?,?)}");
//                        // 设置输入参数的值
//                        cs.setString(1, "0202");
//                        cs.setString(2, "事项");
//                        cs.setString(3, "2023-01-28");
//                        cs.setString(4, "2023-02-22");
//                        cs.setString(5, "aaa");
//                        cs.setString(6, "aaa");
//                        cs.setString(7, "aaa");
//                        cs.setString(8, "aaa");
//                        cs.setString(9, "aaa");
//                        cs.setString(10, "02.0100 张三");
//                        cs.setString(11, "XbQCqobDrk+SO0zPNnlGbyvGYC0=");
//                        cs.registerOutParameter(11, Types.VARCHAR);// 注册输出参数的类型
//                        return cs;
//                    }
//                }, new CallableStatementCallback() {
//                    public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
//                        cs.execute();
//                        String fid = cs.getString(11);
//                        System.out.println("fid:" + fid);
//                        return fid;// 获取输出参数的值
//                    }
//                });
//        log.info("返回值:" + returnVale);
//
//        return returnVale;
//    }


//    /**
//     * 此种写法适合：存储过程返回多个结果集
//     *
//     * @param dbCode
//     * @param sql
//     * @return
//     */
//    public String test(String dbCode, String sql) {
//        JdbcTemplate jdbcTemplate = this.getDataSourceService(dbCode).getJdbcTemplate().getJdbcTemplate();
//
//        String param2Value = (String) jdbcTemplate.execute(
//                new CallableStatementCreator() {
//                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
////                        CallableStatement cs = con.prepareCall("{call [HG_LINK].[hg].[dbo].SyncToDoList (?,?,?,?,?,?,?,?,?,?,?)}");
//                        CallableStatement cs = con.prepareCall("{call [HG_LINK].[hg].[dbo].SyncToDoList (?,?,?,?,?,?,?,?,?,?)}");
//                        // 设置输入参数的值
//                        cs.setString(1, "0202");
//                        cs.setString(2, "事项");
//                        cs.setString(3, "2023-01-28");
//                        cs.setString(4, "2023-01-29");
//                        cs.setString(5, "aaa");
//                        cs.setString(6, "aaa");
//                        cs.setString(7, "aaa");
//                        cs.setString(8, "aaa");
//                        cs.setString(9, "aaa");
//                        cs.setString(10, "02.0100 张三");
////                        cs.registerOutParameter(11, Types.VARCHAR);// 注册输出参数的类型
////                        cs.registerOutParameter(11, OracleTypes.VARCHAR);// 注册输出参数的类型
//                        return cs;
//                    }
//                }, new CallableStatementCallback() {
//                    public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
//                        cs.execute();
////                        String fid = cs.getString(11);
////                        System.out.println("fid:" + fid);
//                        ResultSet resultSet = cs.getResultSet();
//                        System.out.println("resultSet:" + resultSet);
//
//                        if (resultSet != null) {
//                            while (resultSet.next()) {
//                                String fid = resultSet.getString("fid");
//                                System.out.println("fid:" + fid);
//                            }
//                        }
//                        return "";// 获取输出参数的值
//                    }
//                });
//
//        return param2Value;
//    }

}
